-- @owner: wang-tianjing1
-- @date: 2024/8/19
-- @testpoint: after--delete在表上支持行级触发器，使用show triggers查询

@conn PrimaryDbBmode;
--step1:创建表 except:成功
create table test_trigger_src_tb0011(id1 int, id2 int, id3 int);
insert into test_trigger_src_tb0011 values(100,200,300);
create table test_trigger_des_tb0011(id1 int, id2 int, id3 int);
insert into test_trigger_des_tb0011 values(1,2,3);

--step2:创建触发器函数 except:成功
create or replace function tri_delete_func0011() returns trigger as
$$
declare
begin
 delete from test_trigger_des_tb0011 where id1=1;
 return old;
end
$$
language plpgsql;
/

--step3:创建触发器 except:成功
create trigger delete_trigger0011 after delete on test_trigger_src_tb0011
for each row execute procedure tri_delete_func0011();
/

--step4:执行触发事件 except:成功
delete from test_trigger_src_tb0011 where id1=100;
select * from test_trigger_src_tb0011;

--step5:检查触发结果 except:成功
select * from test_trigger_des_tb0011;

--step6:查询触发器，添加where条件 except:成功
show triggers from public where "Trigger"='delete_trigger0011' and "Timing" ='AFTER';

--step7:查询触发器，添加like条件 except:成功
show triggers from public where "Trigger" like 'delete_trigger0011' and "Timing" like 'AFTER';

--step8:清理环境 except:成功
drop trigger if exists delete_trigger0011 on test_trigger_src_tb0011;
drop function tri_delete_func0011() cascade;
drop table test_trigger_src_tb0011;
drop table test_trigger_des_tb0011;